﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sorting.util
{
    /*
     * Excel工具类
     */
    class ExcelHelper
    {

        //根据路径获取项目物理路径并拿到excel列表
        public static List<FileInfo> readLocalPriceExcel(string readPath)
        {
            string path = string.Concat(System.AppDomain.CurrentDomain.BaseDirectory, readPath);
            string[] directoryFiles = new string[] { };
            List<FileInfo> fileInfoList = new List<FileInfo>();
            directoryFiles = System.IO.Directory.GetFiles(path);

            for (int d = 0; d < directoryFiles.Count(); d++)
            {
                FileInfo fileInfo = new FileInfo(directoryFiles[d]);
                fileInfoList.Add(fileInfo);
            }

            return fileInfoList;
        }

        // 获取表格中的数据  
        public static Dictionary<string, System.Data.DataTable> LoadExcel(string pPath, string start, string end)
        {

            string connString = "Driver={Driver do Microsoft Excel(*.xls)};"
                + "DriverId=790;"
                + "SafeTransactions=0;"
                + "ReadOnly=1;"
                + "MaxScanRows=16;"
                + "Threads=3;"
                + "MaxBufferSize=2024;"
                + "UserCommitSync=Yes;"
                + "FIL=excel 8.0;"
                + "PageTimeout=5;"
                + "HDR=no;";  //连接字符串    

            //简单解释下这个连续字符串，Driver={Driver do Microsoft Excel(*.xls)} 这种连接写法不需要创建一个数据源DSN，DRIVERID表示驱动ID，Excel2003后都使用790，  
            //FIL表示Excel文件类型，Excel2007用excel 8.0，MaxBufferSize表示缓存大小， 如果你的文件是2010版本的，也许会报错，所以要找到合适版本的参数设置。  

            connString += "DBQ=" + pPath; //DBQ表示读取Excel的文件名（全路径）  
            OdbcConnection conn = new OdbcConnection(connString);
            OdbcCommand cmd = new OdbcCommand();
            cmd.Connection = conn;

            //获取Excel中所有Sheet名称，作为查询时的表名  
            List<string> sheetNames = GetExcelSheetName(pPath);
            Dictionary<string, System.Data.DataTable> dic = new Dictionary<string, System.Data.DataTable>();

            OdbcDataAdapter da = new OdbcDataAdapter();
            DataSet ds = new DataSet();

            try
            {
                foreach (string sheetName in sheetNames)
                {

                    //存储 整表视图   
                    string sql = "select * from [" + sheetName.Replace('.', '#') + "$" + start + ":" + end + "]";
                    cmd.CommandText = sql;
                    da = new OdbcDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds);
                    System.Data.DataTable dataTable1 = ds.Tables[0];
                    dic.Add(sheetName, dataTable1);
                }

                return dic;  //返回Excel数据中的表名和内容，保存在Dictionary中  
            }
            catch (Exception)
            {
                ds = null;
                throw new Exception("从Excel文件中获取数据时发生错误！可能是Excel版本问题，可以考虑降低版本或者修改连接字符串值");
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                da.Dispose();
                da = null;
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn = null;
            }
        }

        // 获取所有工作表名称  
        private static List<string> GetExcelSheetName(string pPath)
        {
            //打开一个Excel应用  
            Microsoft.Office.Interop.Excel.Application excelApp;
            Workbook excelWB;//创建工作簿（WorkBook：即Excel文件主体本身）  
            Workbooks excelWBs;
            //Worksheet excelWS;//创建工作表（即Excel里的子表sheet）  

            Sheets excelSts;

            excelApp = new Microsoft.Office.Interop.Excel.Application();
            if (excelApp == null)
            {
                throw new Exception("打开Excel应用时发生错误！");
            }
            excelWBs = excelApp.Workbooks;
            //打开一个现有的工作薄  
            excelWB = excelWBs.Add(pPath);
            excelSts = excelWB.Sheets;

            //遍历所有表
            List<string> sheetNames = new List<string>();
            for (int i = 0; i < excelSts.Count; i++)
            {
                sheetNames.Add(excelSts.get_Item(i + 1).Name);
            }

            //选择第一个Sheet页  
            //excelWS = excelSts.get_Item(1);
            //string sheetName = excelWS.Name;

            //ReleaseCOM(excelWS);
            ReleaseCOM(excelSts);
            ReleaseCOM(excelWB);
            ReleaseCOM(excelWBs);
            excelApp.Quit();
            ReleaseCOM(excelApp);
            return sheetNames;
        }

        // 释放资源  
        private static void ReleaseCOM(object pObj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
            }
            catch
            {
                throw new Exception("释放资源时发生错误！");
            }
            finally
            {
                pObj = null;
            }
        }
    }
}
